﻿using KZ.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using KZ.Express.H.ENT;
using KZ.Express.H.BLL;

namespace KZ.Express.H.Presenter
{
    public class CustomerExportPresenter : BasePresenter
    {
        public CustomerExportPresenter(IBaseView view)
            : base(view)
        {
            _view = view;
            _baseBLL = new BaseBLL();
        }

        private void BorderAround(Excel.Range range)
        {
            Excel.Borders borders = range.Borders;
            borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

            Marshal.ReleaseComObject(borders);
        }

        public DataTable LoadOrder(DateTime from, DateTime to, int customerID)
        {
            try
            {
                SearchCriteria search = new SearchCriteria();
                search.Add("From", from);
                search.Add("To", to);
                //search.Add("CarrierID", carrierID);
                search.Add("CustomerID", customerID);

                return _baseBLL.GetDataTable(OrderENT.SelectExportProcedure, search);
            }
            catch (DataAccessException ex)
            {
                throw new BusinessLogicException(ex.Message);
            }
        }

        public void Export(string fileName, DataRowView customerRow, DataTable dt, int rateCount, string template, string exchangerate)
        {
            Excel.Application xlApp = null;
            Excel.Workbooks xlWorkBooks = null;
            Excel.Workbook xlWorkBook = null;
            Excel.Worksheet xlWorkSheet = null;

            try
            {
                int rowCount = dt.Rows.Count;

                _view.UpdateValueProgressBar(0);
                _view.ChangeProgressBarVisible(true);
                _view.ChangeProgressBarMaximum(rowCount);

                xlApp = new Excel.Application();
                xlWorkBooks = xlApp.Workbooks;
                xlWorkBook = xlWorkBooks.Open(template, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                xlWorkSheet = xlWorkBook.ActiveSheet as Excel.Worksheet;

                xlWorkSheet.Cells[9, 3] = "Company Name: " + Utility.StringParse(customerRow["CustomerName"]);
                xlWorkSheet.Cells[10, 3] = "Address: " + Utility.StringParse(customerRow["Address"]);
                xlWorkSheet.Cells[11, 3] = "Attn: " + Utility.StringParse(customerRow["Contact"]);
                xlWorkSheet.Cells[12, 3] = "Tel: " + Utility.StringParse(customerRow["Phone"]);

                int startRow = 15;
                int rowIndex = 15;
                decimal total = 0;
                for (int i = 0; i < rowCount; i++)
                {
                    xlWorkSheet.Cells[rowIndex, 2] = i + 1;
                    xlWorkSheet.Cells[rowIndex, 3] = dt.Rows[i]["OrderDate"];
                    xlWorkSheet.Cells[rowIndex, 4] = "'" + dt.Rows[i]["OrderNumber"];
                    xlWorkSheet.Cells[rowIndex, 5] = dt.Rows[i]["CountryName"];
                    xlWorkSheet.Cells[rowIndex, 6] = dt.Rows[i]["Type"];
                    xlWorkSheet.Cells[rowIndex, 7] = dt.Rows[i]["Weight"];
                    xlWorkSheet.Cells[rowIndex, 8] = dt.Rows[i]["ListPrice"];
                    xlWorkSheet.Cells[rowIndex, 9] = dt.Rows[i]["Fuel"];
                    xlWorkSheet.Cells[rowIndex, 10] = dt.Rows[i]["OrderCharge"];
                    xlWorkSheet.Cells[rowIndex, 11] = dt.Rows[i]["VAT"];
                    xlWorkSheet.Cells[rowIndex, 12] = dt.Rows[i]["TotalRate"];

                    //Calculate total rate
                    total += Utility.DecimalParse(dt.Rows[i]["TotalRate"]);

                    rowIndex++;
                    _view.PerformStepProgressBar(1);
                }

                xlWorkSheet.Range[xlWorkSheet.Cells[startRow, 12], xlWorkSheet.Cells[rowIndex - 1, 12]].Font.Bold = true;
                xlWorkSheet.Range[xlWorkSheet.Cells[startRow, 2], xlWorkSheet.Cells[rowIndex - 1, 3]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                xlWorkSheet.Range[xlWorkSheet.Cells[startRow, 4], xlWorkSheet.Cells[rowIndex - 1, 6]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                xlWorkSheet.Range[xlWorkSheet.Cells[startRow, 7], xlWorkSheet.Cells[rowIndex - 1, 12]].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                xlWorkSheet.Range[xlWorkSheet.Cells[startRow, 7], xlWorkSheet.Cells[rowIndex - 1, 12]].NumberFormat = "#,###.00;[Red](#,###.00);0.00";

                BorderAround(xlWorkSheet.Range[xlWorkSheet.Cells[15, 2], xlWorkSheet.Cells[rowCount + 14, 12]]);
                //TOTAL
                xlWorkSheet.Cells[rowIndex, 11] = "TOTAL:";
                xlWorkSheet.Cells[rowIndex, 11].Font.Bold = true;
                xlWorkSheet.Cells[rowIndex, 11].Font.Size = 12;
                xlWorkSheet.Cells[rowIndex, 12] = total;
                xlWorkSheet.Cells[rowIndex, 12].NumberFormat = "#,##0";
                xlWorkSheet.Cells[rowIndex, 12].Font.Bold = true;
                xlWorkSheet.Cells[rowIndex, 12].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex, 12], xlWorkSheet.Cells[rowIndex, 12]].NumberFormat = "#,###.00;[Red](#,###.00);0.00";
                BorderAround(xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex, 11], xlWorkSheet.Cells[rowIndex, 12]]);

                rowIndex += 1;

                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex, 2], xlWorkSheet.Cells[rowIndex, 9]].Merge();
                xlWorkSheet.Cells[rowIndex, 2] = "Exchange Rate: " + exchangerate + " vnd/usd";
                rowIndex += 1;

                using (StreamReader sr = new StreamReader(Path.Combine(Utility.AppPath, @"Template\Debit noted footer.txt")))
                {
                    //Footer content
                    while (!sr.EndOfStream)
                    {
                        xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex, 2], xlWorkSheet.Cells[rowIndex, 9]].Merge();
                        xlWorkSheet.Cells[rowIndex, 2] = sr.ReadLine();
                        rowIndex++;
                    }
                }


                //Signature
                xlWorkSheet.Range[xlWorkSheet.Cells[rowIndex, 10], xlWorkSheet.Cells[rowIndex, 12]].Merge();
                xlWorkSheet.Cells[rowIndex, 10].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                xlWorkSheet.Cells[rowIndex, 10] = "HCM, " + DateTime.Now.ToString("MMM dd,yyyy");
                xlWorkSheet.Cells[rowIndex, 10].Font.Size = 11;
                xlWorkSheet.Cells[rowIndex + 1, 11] = "Accountant";

                //// save the application
                xlApp.DisplayAlerts = false;
                xlWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                _view.UpdateValueProgressBar(rowCount);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                //Log.LogEx(Message.BLM_ERR_GENERAL.ToString(), Log.LogType.Error, ex);
            }
            finally
            {
                if (xlApp != null)
                {
                    xlApp.Quit();
                    //// these are some cleanup calls
                    System.Threading.Thread.Sleep(1000);
                }

                if (xlWorkSheet != null) Marshal.FinalReleaseComObject(xlWorkSheet);
                if (xlWorkBook != null) Marshal.ReleaseComObject(xlWorkBook);
                if (xlWorkBooks != null) Marshal.ReleaseComObject(xlWorkBooks);
                if (xlApp != null) Marshal.ReleaseComObject(xlApp);

                xlWorkSheet = null;
                xlWorkBook = null;
                xlWorkBooks = null;
                xlApp = null;
                _view.ChangeProgressBarVisible(false);
            }
        }
    }
}
